package com.lisa.auto.tools.seveneleven;

import com.lisa.auto.entity.seveneleven.po.SevenElevenShopAddrPo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author huangzhigang 2020/1/3
 */
public class DataBaseConnect {

    static final Logger logger = LoggerFactory.getLogger(DataBaseConnect.class);

    /** 外部店铺ID */
    protected static final String EXTERNAL_NUM = "external_num";
    /** 地址 */
    protected static final String ADDRESS_DETAILS = "address_details";
    /** 地址编号 */
    protected static final String ADDRESS_NUM = "address_num";

    protected static final String DRIVER_ = "com.mysql.cj.jdbc.Driver";

    protected static final String URL_ = "jdbc:mysql://rm-wz9499bd4c6z3v2pbmo.mysql.rds.aliyuncs.com:3306/ift_waybill?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";

    protected static final String USERNAME = "eckbsadmin";

    protected static final String PASS_WORD = "zZHp7@BedlZD#eAi";

    public static Connection getConnect(){
        Connection conn = null;
        try {
            Class.forName(DRIVER_);
            conn = DriverManager.getConnection(URL_, USERNAME, PASS_WORD);
        } catch (ClassNotFoundException e) {
            logger.info(e.getMessage(), e);
        } catch (SQLException e1) {
            logger.info(e1.getMessage(), e1);
        } catch (Exception e2) {
            logger.info(e2.getMessage(), e2);
        }
        return conn;
    }

    public static List<SevenElevenShopAddrPo> execute(int compNum){
        String SQL_ = String.format("SELECT external_num, address_details, address_num FROM t_address WHERE brand_id = %s AND `status` = 'EFFECTIVE' AND external_num is not null", compNum);
        List<SevenElevenShopAddrPo> shopAddrList = new ArrayList<>();
        try {
            Connection conn = getConnect();
            Statement Statement = conn.createStatement();
            ResultSet rs = Statement.executeQuery(SQL_);
            while (rs.next()) {
                SevenElevenShopAddrPo shopAddr = new SevenElevenShopAddrPo();
                shopAddr.setShopId(rs.getString(EXTERNAL_NUM));
                shopAddr.setShopAddr(rs.getString(ADDRESS_DETAILS));
                shopAddr.setShopAddrNo(rs.getString(ADDRESS_NUM));
                shopAddrList.add(shopAddr);
            }
            rs.close();
            conn.close();
        }catch (Exception e){
            logger.info(e.getMessage(), e);
        }
        return shopAddrList;
    }

}
